﻿namespace ScrumFactory.Data.Sql {
    using System.Collections.Generic;
    using System.ComponentModel.Composition;
    using System.Data.Objects;
    using System.Data.EntityClient;
    using System.Transactions;
    using System.Linq;

    [Export(typeof(ITasksRepository))]
    public class SqlTasksRepository : ITasksRepository {
        private string connectionString;

        [ImportingConstructor()]
        public SqlTasksRepository(
            [Import("ScrumFactoryEntitiesConnectionString")]
            string connectionString) {
            this.connectionString = connectionString;
        }

        public ICollection<Task> GetProjectTasks(string projectUId, System.DateTime fromDate, System.DateTime toDate, bool dailyTasksOnly) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                
                IQueryable<Task> tasks = context.Tasks.Include("TaskInfo").Where(t => t.ProjectUId == projectUId);

                if (!dailyTasksOnly && !fromDate.Equals(System.DateTime.MinValue))
                    tasks = tasks.Where(t => t.CreatedAt >= fromDate);

                if (!dailyTasksOnly && !toDate.Equals(System.DateTime.MinValue))
                    tasks = tasks.Where(t => t.CreatedAt <= toDate);

                if (dailyTasksOnly) {
                    System.DateTime lastDay = System.DateTime.Today.AddDays(-1);
                    if (lastDay.DayOfWeek == System.DayOfWeek.Saturday)
                        lastDay = lastDay.AddDays(-1);
                    if (lastDay.DayOfWeek == System.DayOfWeek.Sunday)
                        lastDay = lastDay.AddDays(-2);
                    if (lastDay.DayOfWeek == System.DayOfWeek.Monday)
                        lastDay = lastDay.AddDays(-3);

                    tasks = tasks.Where(t => t.Status <= (short)TaskStatus.WORKING_ON_TASK || t.EndDate >= lastDay);
                }

                return tasks.ToList();
            }
        }

        public ICollection<Task> GetItemTasks(string backlogItemUId) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                return context.Tasks.Where(t => t.BacklogItemUId == backlogItemUId).ToList();
            }
        }


        public ICollection<Task> GetUserTasks(string taskAssigneeUId) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                return context.Tasks.Include("TaskInfo").Where(t => t.TaskAssigneeUId == taskAssigneeUId && t.Status != (short)TaskStatus.DONE_TASK && t.Status != (short)TaskStatus.CANCELED_TASK).ToList();
            }
        }

        public ICollection<Task> GetUnassignedTasksFromProjects(string[] projectsUIds) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                IQueryable<Task> tasks = context.Tasks.Include("TaskInfo").Where(t => t.TaskAssigneeUId == null && t.Status != (short)TaskStatus.DONE_TASK && t.Status != (short)TaskStatus.CANCELED_TASK);
                if (projectsUIds != null && projectsUIds.Length > 0)
                    tasks = tasks.Where(t => projectsUIds.Contains(t.ProjectUId));
                return tasks.ToList();
            }
        }

        public ICollection<Task> GetUserOwnedTasks(string taskOwnerUId) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                var ownedtasks = context.Tasks.Include("TaskInfo").Where(t => t.TaskOwnerUId == taskOwnerUId && (t.TaskAssigneeUId != taskOwnerUId || t.TaskAssigneeUId==null) && t.Status != (short)TaskStatus.DONE_TASK && t.Status != (short)TaskStatus.CANCELED_TASK);
                ownedtasks = ownedtasks.Where(t => context.Projects.Any(p => p.ProjectUId == t.ProjectUId && (p.Status == 3 || p.Status == 5))); // only running projects
                return ownedtasks.ToList();
            }
        }

        public ICollection<BacklogItemEffectiveHours> GetItemTasksEffectiveHoursByProject(string projectUId) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                return context.BacklogItemEffectiveHours.Where(t => t.ProjectUId == projectUId).ToList();
            }
        }

        public decimal GetTotalEffectiveHoursByItem(string backlogitemUId) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                decimal? hrs = context.Tasks.Where(t => t.BacklogItemUId == backlogitemUId).Sum(t => (decimal?) t.EffectiveHours);
                if (!hrs.HasValue)
                    return 0;
                return hrs.Value;
            }
        }

        public ICollection<TodayMemberPlannedHours> GetTodayMemberPlannedHoursByProjectUId(string projectUId) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                return context.TodayMemberPlannedHours.Where(m => context.ProjectMemberships.Any(ms => ms.MemberUId == m.TaskAssigneeUId && ms.ProjectUId==projectUId)).ToList();
            }
        }

        public ICollection<TodayMemberPlannedHours> GetTodayMemberPlannedHoursByUIds(string[] membersUIds) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                return context.TodayMemberPlannedHours.Where(m => membersUIds.Contains(m.TaskAssigneeUId)).ToList();
            }
        }

        public Task GetTask(string taskUId) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                return context.Tasks.Include("TaskDetail").Where(t => t.TaskUId == taskUId).SingleOrDefault();
            }
        }

        public Task GetTaskByNumber(int taskNumber) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                return context.Tasks.Include("TaskDetail").Where(t => t.TaskNumber == taskNumber).SingleOrDefault();
            }
        }

        public TaskDetail GetTaskDetail(string taskUId) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                return context.TaskDetails.Where(t => t.TaskUId == taskUId).SingleOrDefault();
            }
        }


        public decimal GetTotalEffectiveHoursByRole(string roleUId) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                decimal? hours = context.Tasks.Where(t => t.RoleUId == roleUId).Sum(t => (decimal?) t.EffectiveHours);
                if (!hours.HasValue)
                    return 0;
                return hours.Value;
            }
        }

        public void SaveTask(Task task) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                Task oldTask = GetTask(task.TaskUId);

                if (oldTask == null) {
                    context.Tasks.AddObject(task);
                    if(task.TaskDetail!=null)
                        context.TaskDetails.AddObject(task.TaskDetail);
                } else {

                    // apply current values, but preserve create at date
                    System.DateTime createAt = oldTask.CreatedAt;
                    string owner = oldTask.TaskOwnerUId;

                    context.AttachTo("Tasks", oldTask);
                    context.ApplyCurrentValues<Task>("Tasks", task);
                    oldTask.CreatedAt = createAt;
                    oldTask.TaskOwnerUId = owner;

                    if (task.TaskDetail != null && oldTask.TaskDetail==null) {
                        context.TaskDetails.AddObject(task.TaskDetail);
                    }
                    else
                    if (task.TaskDetail != null && oldTask.TaskDetail != null) {
                        context.AttachTo("TaskDetails", oldTask.TaskDetail);
                        context.ApplyCurrentValues<TaskDetail>("TaskDetails", task.TaskDetail);
                    }
                }

                context.SaveChanges();
             
            }
        }

        public decimal GetTotalEffectiveHoursByProject(string projectUId) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                decimal? hours = context.Tasks.Where(t => t.ProjectUId == projectUId).Sum(t => (decimal?)t.EffectiveHours);
                if (!hours.HasValue)
                    return 0;
                return hours.Value;
            }
        }

        public decimal GetTotalBugHoursByProject(string projectUId) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                decimal? hours = context.Tasks.Where(t => t.ProjectUId == projectUId && (t.TaskType==(short)TaskTypes.BUG_TASK || t.TaskType==(short)TaskTypes.AFTER_DEPLOY_BUG_TASK)).Sum(t => (decimal?)t.EffectiveHours);
                if (!hours.HasValue)
                    return 0;
                return hours.Value;
            }
        }

        public bool DoesBacklogItemHasTasks(string backlogItemUId) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                return context.Tasks.Any(t => t.BacklogItemUId == backlogItemUId);
            }
        }

        public bool DoesMemberHasAnyTaskAtProject(string projectUId, string memberUId) {
            using (var context = new ScrumFactoryEntities(this.connectionString)) {
                return context.Tasks.Any(t => t.ProjectUId ==projectUId && t.TaskAssigneeUId==memberUId);
            }
        }

    }
}
